package by.bsu.touragency.db.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.Country;
import by.bsu.touragency.db.beans.Hotel;
import by.bsu.touragency.db.beans.Tour;
import by.bsu.touragency.db.beans.Transfer;
import by.bsu.touragency.db.beans.Transport;
import by.bsu.touragency.db.connection.DBConnector;

public class TourDAO extends AbstractDAO {

	public static final String SQL_TOUR_CREATE = "INSERT INTO tourAgencyDB.tour (transferId, price, hotelId, transportId, dateTo, dateFrom, quantity)" + "VALUES (?, ?, ?, ?, ?, ?,?);";
	public static final String SQL_TOUR_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.tour " + " WHERE dateTo = ? AND dateFrom = ? AND price = ? AND transferId = ?;";
	public static final String SQL_TOUR_READ_BY_ID = "SELECT * FROM tourAgencyDB.tour " + "WHERE (id= ?);";
	public static final String SQL_TOUR_DELETE = "DELETE FROM tourAgencyDB.tour WHERE id = ?";
	public static final String SQL_TOUR_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.bookedTour WHERE tourId = ? ;";
	public static final String SQL_TOUR_READ_BY_COUNTRY = "SELECT * FROM tourAgencyDB.tour " + "WHERE (countryId= ?);";
	private static final String SQL_TOUR_READ_ALL_NB = "SELECT * FROM tourAgencyDB.tour ;";
	private static final String SQL_TOUR_IS_EXIST = "SELECT COUNT(*) AS rowcount FROM tourAgencyDB.tour WHERE hotelId=? AND dateTo=? AND dateFrom=?;";
	private static final String SQL_TOUR_UPDATE_QUANTITY = "UPDATE tourAgencyDB.tour  SET quantity = ? WHERE (id = ?);";
	private static final String SQL_TOUR_UPDATE_PRICE = "UPDATE tourAgencyDB.tour  SET price = ? WHERE (id = ?);";
	private static final String SQL_TOUR_UPDATE_TRANSFER = "UPDATE tourAgencyDB.tour  SET transferId = ? WHERE (id = ?);";
	private static final String SQL_TOUR_UPDATE_TRANSPORT = "UPDATE tourAgencyDB.tour  SET transportId = ? WHERE (id = ?);";

	private static Logger logger = Logger.getLogger("log");

	private boolean isExist(Tour tour) {
		logger.info("Is this tour exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_IS_EXIST);
			int n;
			try {// 3
				statement.setInt(1, tour.getHotel().getId());
				java.sql.Date date = new Date(tour.getDateTo()
						.getTimeInMillis());
				statement.setDate(2, date);
				date = new Date(tour.getDateFrom().getTimeInMillis());
				statement.setDate(3, date);
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("This tour type is already exist");
				return true;
			} else {
				logger.debug("This tour type is NOT exist");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} 
	}

	private int isUsed(int id) {
		logger.info("Is this tour in use");
		try {// 1
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (statement != null) {
				statement.close();
			}
			return n;
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return -1;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return -1;
		}
	}

	// C
	public Tour create(Tour tour) {
		if (!isExist(tour)) {
			logger.info("Create new tour");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TOUR_CREATE);
				statement.setInt(1, tour.getTransfer().getId());
				statement.setInt(2, tour.getPrice());
				statement.setInt(3, tour.getHotel().getId());
				statement.setInt(4, tour.getTransport().getId());
				java.sql.Date date = new Date(tour.getDateTo()
						.getTimeInMillis());
				statement.setDate(5, date);
				date = new Date(tour.getDateFrom().getTimeInMillis());
				statement.setDate(6, date);
				statement.setInt(7, tour.getQuantity());
				statement.executeUpdate();

				statement = connector
						.getPreparedStatement(SQL_TOUR_CREATE_GET_ID);
				date = new Date(tour.getDateTo().getTimeInMillis());
				statement.setDate(1, date);
				date = new Date(tour.getDateFrom().getTimeInMillis());
				statement.setDate(2, date);
				statement.setInt(3, tour.getPrice());
				statement.setInt(4, tour.getTransfer().getId());
				resultSet = statement.executeQuery();

				if (resultSet.next()) {
					tour.setId(resultSet.getInt("id"));
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
		return tour;
	}

	// R
	public Tour readById(int id) {
		logger.info("Read tour by id");
		Tour tour = new Tour();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				tour.setId(resultSet.getInt("id"));
				tour.setPrice(resultSet.getInt("price"));
				tour.setQuantity(resultSet.getInt("quantity"));

				int hotelId = resultSet.getInt("hotelId");
				Hotel hotel = new Hotel();
				HotelDAO hotelDAO = new HotelDAO();
				hotel = hotelDAO.readById(hotelId);
				tour.setHotel(hotel);

				int transportId = resultSet.getInt("transportId");
				Transport transport = new Transport();
				TransportDAO transportDAO = new TransportDAO();
				transport = transportDAO.readById(transportId);
				tour.setTransport(transport);

				int transferId = resultSet.getInt("transferId");
				Transfer transfer = new Transfer();
				TransferDAO transferd = new TransferDAO();
				transfer = transferd.readByID(transferId);
				tour.setTransfer(transfer);

				GregorianCalendar dateTo = new GregorianCalendar();
				java.sql.Date date = resultSet.getDate("dateTo");
				dateTo.setTimeInMillis(date.getTime());
				tour.setDateTo(dateTo);

				GregorianCalendar dateFrom = new GregorianCalendar();
				date = resultSet.getDate("dateFrom");
				dateFrom.setTimeInMillis(date.getTime());
				tour.setDateFrom(dateFrom);
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return tour;
	}

	public ArrayList<Tour> readByCountry(String Country) {
		logger.info("Read all tours in this country");
		ArrayList<Tour> tours = new ArrayList<Tour>();
		CountryDAO cd = new CountryDAO();
		Country country = new Country();
		try {
			country = cd.readByName(Country);
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_READ_BY_COUNTRY);
			statement.setInt(1, country.getId());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Tour tour = new Tour();
				tour.setId(resultSet.getInt("id"));
				tour.setPrice(resultSet.getInt("price"));

				int hotelId = resultSet.getInt("hotelId");
				Hotel hotel = new Hotel();
				HotelDAO hotelDAO = new HotelDAO();
				hotel = hotelDAO.readById(hotelId);
				tour.setHotel(hotel);

				int transportId = resultSet.getInt("transportId");
				Transport transport = new Transport();
				TransportDAO transportDAO = new TransportDAO();
				transport = transportDAO.readById(transportId);
				tour.setTransport(transport);

				GregorianCalendar dateTo = new GregorianCalendar();
				java.sql.Date date = resultSet.getDate("dateTo");
				dateTo.setTimeInMillis(date.getTime());
				tour.setDateTo(dateTo);

				GregorianCalendar dateFrom = new GregorianCalendar();
				date = resultSet.getDate("dateFrom");
				dateFrom.setTimeInMillis(date.getTime());
				tour.setDateFrom(dateFrom);

				tours.add(tour);
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return tours;
	}

	public ArrayList<Tour> readNB() {
		logger.info("Read all not booked tours");
		ArrayList<Tour> arr = new ArrayList<Tour>();
		Tour tour = new Tour();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_READ_ALL_NB);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {

				if (resultSet.getInt("quantity")
						- isUsed(resultSet.getInt("id")) > 0) {
					tour = new Tour();
					tour.setId(resultSet.getInt("id"));
					tour.setPrice(resultSet.getInt("price"));

					int hotelId = resultSet.getInt("hotelId");
					Hotel hotel = new Hotel();
					HotelDAO hotelDAO = new HotelDAO();
					hotel = hotelDAO.readById(hotelId);
					tour.setHotel(hotel);

					int transportId = resultSet.getInt("transportId");
					Transport transport = new Transport();
					TransportDAO transportDAO = new TransportDAO();
					transport = transportDAO.readById(transportId);
					tour.setTransport(transport);

					int transferId = resultSet.getInt("transferId");
					Transfer transfer = new Transfer();
					TransferDAO transferd = new TransferDAO();
					transfer = transferd.readByID(transferId);
					tour.setTransfer(transfer);

					GregorianCalendar dateTo = new GregorianCalendar();
					java.sql.Date date = resultSet.getDate("dateTo");
					dateTo.setTimeInMillis(date.getTime());
					tour.setDateTo(dateTo);

					GregorianCalendar dateFrom = new GregorianCalendar();
					date = resultSet.getDate("dateFrom");
					dateFrom.setTimeInMillis(date.getTime());
					tour.setDateFrom(dateFrom);

					arr.add(tour);
				}
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return arr;
	}

	// U
	public void updateQuantity(Tour tour) {
		logger.info("Update quantity in this tour");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_UPDATE_QUANTITY);
			statement.setInt(1, tour.getQuantity());
			statement.setInt(2, tour.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updatePrice(Tour tour) {
		logger.info("Update quantity in this tour");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_UPDATE_PRICE);
			statement.setInt(1, tour.getPrice());
			statement.setInt(2, tour.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updateTransport(Tour tour) {
		logger.info("Update transport in this tour");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_UPDATE_TRANSPORT);
			statement.setInt(1, tour.getTransport().getId());
			statement.setInt(2, tour.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updateTransfer(Tour tour) {
		logger.info("Update transfer in this tour");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TOUR_UPDATE_TRANSFER);
			statement.setInt(1, tour.getTransfer().getId());
			statement.setInt(2, tour.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// D
	public void deleteById(Tour tour) {
		logger.info("Delete this tour");
		if (tour.getQuantity() - isUsed(tour.getId()) > 0) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TOUR_DELETE);
				statement.setInt(1, tour.getId());
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}

}
